﻿using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using ST.Buz.Base;
using ST.Buz.Base.Entity;
using ST.Buz.Base.Enums;
using ST.Buz.Base.ViewModel;
using ST.Common.Data;
using ST.Common.Util;
using ST.Common.Util.Model;
using ST.Common.Util.Mvc;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace ST.Buz.Service
{
	/// <summary>
	/// 只针对当前项目的扩展接口定义
	/// </summary>
	public partial interface IServiceSysOrganization
	{
	}

	/// <summary>
	/// 只针对当前项目的扩展接口实现
	/// </summary>
	public partial class ServiceSysOrganization
	{
		public List<VMI_Sys_Area> ParseExcel(string filePath)
		{
			List<VMI_Sys_Area> lst = null;
			try
			{
				if (string.IsNullOrEmpty(filePath))
				{
					throw new ArgumentException("excel文件不能为空");
				}
				if (!File.Exists(filePath))
				{
					throw new ArgumentException("excel文件" + filePath + "不存在");
				}
				using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
				{
					IWorkbook wb = WorkbookFactory.Create(fs);
					var ws = wb.GetSheetAt(0);
					var totalRows = ws.LastRowNum;
					//var totalCols = rowHeader.LastCellNum;
					lst = new List<VMI_Sys_Area>();
					StringBuilder bufError = new StringBuilder();
					int[] pidxArea = new int[3];
					int[] pidxOrg = new int[2];
					Dictionary<string, int[]> dicAreaIndex = new Dictionary<string, int[]>();
					Dictionary<string, int[]> dicOrgIndex = new Dictionary<string, int[]>();
					int maxErrors = 20;
					for (int index = 1; index <= totalRows; index++)//数据从第2行开始
					{
						try
						{
							var row = ws.GetRow(index);
							if (row.Cells.Count == 0)
							{
								break;
							}
							ICell cell0 = row.GetCell(0);
							ICell cell1 = row.GetCell(1);
							ICell cell2 = row.GetCell(2);
							ICell cell3 = row.GetCell(3);
							ICell cell4 = row.GetCell(4);
							if ((cell0 == null || cell0.CellType == CellType.Blank)
								&& (cell1 == null || cell1.CellType == CellType.Blank)
								&& (cell2 == null || cell2.CellType == CellType.Blank)
								&& (cell3 == null || cell3.CellType == CellType.Blank)
								&& (cell4 == null || cell4.CellType == CellType.Blank)
							)
							{
								break;
							}
							if (cell0 == null || CellType.String != cell0.CellType)
							{
								throw new BuzException("第" + (cell0.ColumnIndex + 1) + "列为空或类型不为字符串");
							}
							if (cell1 == null || CellType.String != cell1.CellType)
							{
								throw new BuzException("第" + (cell1.ColumnIndex + 1) + "列为空或类型不为字符串");
							}
							if (cell2 == null || CellType.String != cell2.CellType)
							{
								throw new BuzException("第" + (cell2.ColumnIndex + 1) + "列为空或类型不为字符串");
							}
							if (cell3 == null || CellType.String != cell3.CellType)
							{
								throw new BuzException("第" + (cell3.ColumnIndex + 1) + "列为空或类型不为字符串");
							}
							if (cell4 == null || CellType.String != cell4.CellType)
							{
								throw new BuzException("第" + (cell4.ColumnIndex + 1) + "列为空或类型不为字符串");
							}
							string qy = !string.IsNullOrEmpty(cell0.StringCellValue) ? cell0.StringCellValue.Trim() : "";
							string jd = !string.IsNullOrEmpty(cell1.StringCellValue) ? cell1.StringCellValue.Trim() : "";
							string sq = !string.IsNullOrEmpty(cell2.StringCellValue) ? cell2.StringCellValue.Trim() : "";
							string xq = !string.IsNullOrEmpty(cell3.StringCellValue) ? cell3.StringCellValue.Trim() : "";
							string ld = !string.IsNullOrEmpty(cell4.StringCellValue) ? cell4.StringCellValue.Trim() : "";
							if (qy.Length == 0 || jd.Length == 0 || sq.Length == 0 || xq.Length == 0 || ld.Length == 0)
							{
								throw new BuzException("有值为空");
							}
							pidxArea[0] = -1;
							pidxArea[1] = -1;
							pidxArea[2] = -1;

							if (!dicAreaIndex.ContainsKey(qy))
							{
								pidxArea[0] = lst.Count;
								dicAreaIndex.Add(qy, new int[] { pidxArea[0] });
								lst.Add(new VMI_Sys_Area()
								{
									Name = qy,
									Sort = pidxArea[0],
									Levels = qy
								});
							}
							else
							{
								pidxArea[0] = dicAreaIndex[qy][0];
							}
							var keyJD = qy + "-" + jd;
							if (!dicAreaIndex.ContainsKey(keyJD))
							{
								pidxArea[1] = lst[pidxArea[0]].Children.Count;
								dicAreaIndex.Add(keyJD, new int[] { pidxArea[0], pidxArea[1] });
								lst[pidxArea[0]].Children.Add(new VMI_Sys_Area()
								{
									Name = jd,
									Sort = pidxArea[1],
									Levels = keyJD
								});
							}
							else
							{
								pidxArea[1] = dicAreaIndex[keyJD][1];
							}
							var keySQ = keyJD + "-" + sq;
							if (!dicAreaIndex.ContainsKey(keySQ))
							{
								pidxArea[2] = lst[pidxArea[0]].Children[pidxArea[1]].Children.Count;
								dicAreaIndex.Add(keySQ, new int[] { pidxArea[0], pidxArea[1], pidxArea[2] });
								lst[pidxArea[0]].Children[pidxArea[1]].Children.Add(new VMI_Sys_Area()
								{
									Name = sq,
									Sort = pidxArea[2],
									Levels = keySQ
								});
							}
							else
							{
								pidxArea[2] = dicAreaIndex[keySQ][2];
							}
							if (pidxArea[0] < 0 || pidxArea[1] < 0 || pidxArea[2] < 0)
							{
								throw new BuzException("未找到相关信息");
							}
							var area = lst[pidxArea[0]].Children[pidxArea[1]].Children[pidxArea[2]];
							pidxOrg[0] = -1;
							pidxOrg[1] = -1;
							var keyXQ = keySQ + "-" + xq;
							if (!dicOrgIndex.ContainsKey(keyXQ))
							{
								pidxOrg[0] = area.Organizations.Count;
								dicOrgIndex.Add(keyXQ, new int[] { pidxOrg[0] });
								area.Organizations.Add(new VMI_Sys_Organization()
								{
									Name = xq,
									Sort = pidxOrg[0],
									Levels = keyXQ
								});
							}
							else
							{
								pidxOrg[0] = dicOrgIndex[keyXQ][0];
							}
							var keyLD = keyXQ + "-" + ld;
							if (!dicOrgIndex.ContainsKey(keyLD))
							{
								pidxOrg[1] = area.Organizations[pidxOrg[0]].Children.Count;
								dicOrgIndex.Add(keyLD, new int[] { pidxOrg[0], pidxOrg[1] });
								area.Organizations[pidxOrg[0]].Children.Add(new VMI_Sys_Organization()
								{
									Name = ld,
									Sort = pidxOrg[1],
									Levels = keyLD
								});
							}
							else
							{
								throw new BuzException("数据重复");
							}
						}
						catch (Exception ex)
						{
							bufError.AppendLine("第" + (index + 1) + "行" + ex.Message);
							maxErrors--;
							if (maxErrors < 0)
							{
								break;
							}
						}
					}
					if (bufError.Length > 0)
					{
						throw new BuzException("解析上传文件出错，详细信息如下：" + Environment.NewLine + bufError.ToString());
					}
				}
			}
			catch (BuzException ex)
			{
				logger.LogError(ex.InnerException ?? ex, ex.Message);
				throw ex;
			}
			catch (ArgumentException ex)
			{
				logger.LogError(ex.InnerException ?? ex, ex.Message);
				throw ex;
			}
			catch (Exception ex)
			{
				logger.LogError(ex.InnerException ?? ex, ex.Message);
				throw new BuzException(ex.InnerException != null ? ex.InnerException.Message : ex.Message, ex);
			}
			return lst;
		}

		private void CompareSysOrg(Dictionary<string, int> dicArea, Dictionary<string, int> dicOrg, VMI_Sys_Organization org)
		{
			if (org == null)
			{
				return;
			}
			if (!string.IsNullOrEmpty(org.Levels) && dicOrg.ContainsKey(org.Levels))
			{
				org.ID = dicOrg[org.Levels];
			}
			if (!string.IsNullOrEmpty(org.AreaLevels) && dicArea.ContainsKey(org.AreaLevels))
			{
				org.AreaID = dicArea[org.AreaLevels];
			}
			foreach (var child in org.Children)
			{
				CompareSysOrg(dicArea, dicOrg, child);
			}
		}

		private void CompareSysArea(Dictionary<string, int> dicArea, Dictionary<string, int> dicOrg, VMI_Sys_Area area)
		{
			if (area == null)
			{
				return;
			}
			if (!string.IsNullOrEmpty(area.Levels) && dicArea.ContainsKey(area.Levels))
			{
				area.ID = dicArea[area.Levels];
			}
			foreach (var org in area.Organizations)
			{
				org.AreaLevels = area.Levels;
				CompareSysOrg(dicArea, dicOrg, org);
			}
			foreach (var child in area.Children)
			{
				CompareSysArea(dicArea, dicOrg, child);
			}
		}

		private void InsertOrg(DbConnection conn, DbTransaction trans, DbCommand cmdOrgInsert, VMI_Sys_Organization org, int parentID, int areaID, DateTime dtNow, UserSession sessionCurrent)
		{
			if (org == null)
			{
				return;
			}
			if (org.ID == 0)
			{
				cmdOrgInsert.Parameters.Clear();
				cmdOrgInsert.Parameters.AddRange(new DbParameter[]{
					new SqlParameter("@AreaID",areaID),
					new SqlParameter("@Code",""),
					new SqlParameter("@Name",org.Name),
					new SqlParameter("@PID",parentID),
					new SqlParameter("@AddDate",dtNow),
					new SqlParameter("@AddManID",sessionCurrent.UserID),
					new SqlParameter("@AddManName",sessionCurrent.UserName),

				});
				org.ID = Convert.ToInt32(cmdOrgInsert.ExecuteScalar());
			}

			foreach (var child in org.Children)
			{
				InsertOrg(conn, trans, cmdOrgInsert, child, org.ID, areaID, dtNow, sessionCurrent);
			}
		}

		private void InsertArea(DbConnection conn, DbTransaction trans, DbCommand cmdAreaInsert, DbCommand cmdOrgInsert, VMI_Sys_Area area, int parentID, DateTime dtNow, UserSession sessionCurrent)
		{
			if (area == null)
			{
				return;
			}
			if (area.ID == 0)
			{
				cmdAreaInsert.Parameters.Clear();
				cmdAreaInsert.Parameters.AddRange(new DbParameter[]{
					new SqlParameter("@Name",area.Name),
					new SqlParameter("@PID",parentID),
					new SqlParameter("@AddDate",dtNow),
					new SqlParameter("@AddManID",sessionCurrent.UserID),
					new SqlParameter("@AddManName",sessionCurrent.UserName),
				});
				area.ID = Convert.ToInt32(cmdAreaInsert.ExecuteScalar());
			}

			foreach (var child in area.Children)
			{
				InsertArea(conn, trans, cmdAreaInsert, cmdOrgInsert, child, area.ID, dtNow, sessionCurrent);
			}
			foreach (var org in area.Organizations)
			{
				InsertOrg(conn, trans, cmdOrgInsert, org, 0, area.ID, dtNow, sessionCurrent);
			}
		}

		/// <summary>
		/// 批量导入（使用事务）
		/// </summary>
		/// <param name="lst"></param>
		/// <param name="sessionCurrent"></param>
		/// <param name="trans"></param>
		public void BatchImport(List<VMI_Sys_Area> lst, UserSession sessionCurrent)
		{
			if (lst == null || lst.Count == 0)
			{
				throw new ArgumentException("要导入的列表不能为空");
			}
			if (sessionCurrent == null)
			{
				throw new AuthException("用户未登录或登录已过期");
			}
			//List<string> lstID = new List<string>();
			//Dictionary<string, Sys_Organization> dicOrg = new Dictionary<string, Sys_Organization>();
			//StringBuilder bufError = new StringBuilder();
			//for (i = 0; i < lst.Count; i++)
			//{
			//	//if (!dicOrg.ContainsKey(lst[i].OrgName))
			//	//{
			//	//	dicOrg.Add(lst[i].OrgName, new Sys_Organization()
			//	//	{
			//	//		Name = lst[i].OrgName
			//	//	});
			//	//}
			//	//if (lstID.Contains(lst[i].IDCardNo))
			//	//{
			//	//	bufError.AppendLine("身份证号[" + lst[i].IDCardNo + "]重复");
			//	//}
			//	//lstID.Add(lst[i].IDCardNo);
			//}
			//if (bufError.Length > 0)
			//{
			//	throw new BuzException(bufError.ToString());
			//}
			//if (lstID.Count == 0)
			//{
			//	throw new ArgumentException("要导入的数据不能为空");
			//}
			//string strID = "'" + string.Join("','", lstID) + "'";
			DateTime dtNow = DateTime.Now;
			#region 插入新的数据
			using (var conn = (SqlConnection)context.Database.GetDbConnection())
			{
				conn.Open();

				using (var trans = conn.BeginTransaction())
				{
					try
					{
						//var ctxOpts = new DbContextOptionsBuilder<BuzContext>()
						//	.UseSqlServer(conn)
						//	.Options;
						//using (var ctx = new BuzContext(ctxOpts))
						//{
						//	ctx.Database.UseTransaction(trans);
						//	//var queryOld = from d in ctx.Student where lstYDH.Contains(d.YDH) select d;
						//	var queryOld = from d in ctx.SysOrganization select d;
						//	lstOld = queryOld.AsNoTracking().ToList();

						//	var queryOrg = from d in ctx.SysOrganization where dicOrg.Keys.Contains(d.Name) select d;
						//	lstOrgOld = queryOrg.AsNoTracking().ToList();

						//	string orgPrefix = "000000";
						//	string orgMaxSNo = ctx.SysOrganization.Where(m => EF.Functions.Like(m.Code, orgPrefix + "%")).Max(m => m.Code);
						//	int orgMaxNo = 0;
						//	if (!string.IsNullOrEmpty(orgMaxSNo))
						//	{
						//		int.TryParse(orgMaxSNo.Substring(orgPrefix.Length), out orgMaxNo);
						//	}

						//		org.Code = orgPrefix + (++orgMaxNo).ToString().PadLeft(3, '0');
						//		ctx.SysOrganization.Add(org);
						//	ctx.SaveChanges();
						//}

						DbParameter[] parasArea = new DbParameter[]{
							new SqlParameter("@area_id",SqlDbType.Int),
							new SqlParameter("@errcode",SqlDbType.Int),
							new SqlParameter("@errmsg",SqlDbType.NVarChar,200)
						};
						parasArea[0].Value = 0;
						parasArea[1].Direction = ParameterDirection.Output;
						parasArea[2].Direction = ParameterDirection.Output;
						var lstAreaOld = ExecuteReaderWithinTrans<VMI_Sys_Area>(conn, trans, "sp_sys_area_simple_full_tree", parasArea, CommandType.StoredProcedure);
						Dictionary<string, int> dicArea = new Dictionary<string, int>();
						foreach (var areaOld in lstAreaOld)
						{
							if (string.IsNullOrEmpty(areaOld.Levels) || dicArea.ContainsKey(areaOld.Levels))
							{
								continue;
							}
							dicArea.Add(areaOld.Levels, areaOld.ID);
						}
						SqlParameter[] parasOrg = new SqlParameter[]{
							new SqlParameter("@org_id",SqlDbType.Int),
							new SqlParameter("@errcode",SqlDbType.Int),
							new SqlParameter("@errmsg",SqlDbType.NVarChar,200),
						};
						parasOrg[0].Value = 0;
						parasOrg[1].Direction = ParameterDirection.Output;
						parasOrg[2].Direction = ParameterDirection.Output;
						var lstOrgOld = ExecuteReaderWithinTrans<VMI_Sys_Organization>(conn, trans, "sp_sys_organazation_simple_full_tree", parasOrg, CommandType.StoredProcedure);
						Dictionary<string, int> dicOrg = new Dictionary<string, int>();
						foreach (var orgOld in lstOrgOld)
						{
							if (string.IsNullOrEmpty(orgOld.Levels) || dicOrg.ContainsKey(orgOld.Levels))
							{
								continue;
							}
							dicOrg.Add(orgOld.Levels, orgOld.ID);
						}

						foreach (var area in lst)
						{
							CompareSysArea(dicArea, dicOrg, area);
						}

						var cmdAreaInsert = conn.CreateCommand();
						cmdAreaInsert.Transaction = trans;
						cmdAreaInsert.CommandText = "INSERT INTO Sys_Area(Name,PID,AddDate,AddManID,AddManName) VALUES(@Name,@PID,@AddDate,@AddManID,@AddManName) SELECT SCOPE_IDENTITY();";
						cmdAreaInsert.CommandType = CommandType.Text;

						var cmdOrgInsert = conn.CreateCommand();
						cmdOrgInsert.Transaction = trans;
						cmdOrgInsert.CommandText = "INSERT INTO Sys_Organization(AreaID,Code,Name,PID,AddDate,AddManID,AddManName) VALUES(@AreaID,@Code,@Name,@PID,@AddDate,@AddManID,@AddManName) SELECT SCOPE_IDENTITY();";
						cmdOrgInsert.CommandType = CommandType.Text;

						foreach (var area in lst)
						{
							InsertArea(conn, trans, cmdAreaInsert, cmdOrgInsert, area, 0, dtNow, sessionCurrent);
						}
						//Sys_Compare_Tree<VMI_Sys_Area> compareArea = new Sys_Compare_Tree<VMI_Sys_Area>();
						////Sys_Compare_Tree<VMI_Sys_Organization> compareOrg = new Sys_Compare_Tree<VMI_Sys_Organization>();
						////找出删掉的
						////var lstAreaDeleted = lstAreaOld.Except(lst, compareArea);
						////找出新的
						//var lstAreaNew = lst.Except(lstAreaOld, compareArea).ToList();
						////找出还在的
						////var lstExists = lst.Intersect(lstAreaOld, compareArea);

						#region 标记删除
						//var paras = new DbParameter[]{
						//	new SqlParameter("@OrgID",SqlDbType.Int)
						//};
						//paras[0].Value = 0;
						//ExecuteNonQueryWithinTrans(conn, trans, "", paras);
						#endregion

						#region 更新现有的
						//var paras = new DbParameter[]{
						//	new SqlParameter("@OrgID",SqlDbType.Int)
						//};
						//paras[0].Value = 0;
						#endregion

						#region 批量插入新的
						////if (lstNew.Count > 0)
						//{
						//	DataTable dt = ExecuteReaderWithinTrans(conn, trans, "SELECT * FROM Sys_Area WHERE 1=2", null);
						//	SqlBulkCopyOptions opts = new SqlBulkCopyOptions();
						//	SqlBulkCopy bulk = new SqlBulkCopy(conn, opts, trans);
						//	bulk.DestinationTableName = "Sys_Area";
						//	//bulk.BatchSize = 50;
						//	PropertyInfo[] properties = typeof(Sys_Area).GetProperties();
						//	int[] columnsIndex = new int[dt.Columns.Count];
						//	for (i = 0; i < dt.Columns.Count; i++)
						//	{
						//		columnsIndex[i] = -1;
						//		bulk.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
						//		for (j = 0; j < properties.Length; j++)
						//		{
						//			if (properties[j].Name == dt.Columns[i].ColumnName)
						//			{
						//				columnsIndex[i] = j;
						//				break;
						//			}
						//		}
						//	}
						//	foreach (var m in lstNew)
						//	{
						//		try
						//		{
						//			if (string.IsNullOrEmpty(m.IDCardNo))//跳过关键信息为空
						//			{
						//				continue;
						//			}
						//			m.OrgID = dicOrg[m.OrgName].ID;//设置学校
						//			var row = dt.NewRow();
						//			for (j = 0; j < columnsIndex.Length; j++)
						//			{
						//				if (columnsIndex[j] < 0)
						//				{
						//					continue;
						//				}
						//				var val = properties[columnsIndex[j]].GetValue(m);
						//				if (val != null)
						//				{
						//					row[dt.Columns[j].ColumnName] = val;
						//				}
						//			}
						//			row["UserID"] = 0;
						//			//row["AddDate"] = dtNow;
						//			//row["AddManID"] = sessionCurrent.UserID;
						//			//row["AddManName"] = sessionCurrent.UserName;
						//			dt.Rows.Add(row);
						//		}
						//		catch (Exception ex)
						//		{
						//			throw new Exception("新增学生【" + m.Name + "】出错，" + ex.Message);
						//		}
						//	}
						//	bulk.WriteToServer(dt);
						//}
						#endregion

						//更新单位信息里的冗余街道信息
						ExecuteNonQueryWithinTrans(conn, trans, "UPDATE Sys_Organization SET JDID=Sys_Area.PID FROM Sys_Area WHERE Sys_Organization.AreaID=Sys_Area.ID", null);
						//更新单位信息里的冗余区域信息
						ExecuteNonQueryWithinTrans(conn, trans, "UPDATE Sys_Organization SET QYID=Sys_Area.PID FROM Sys_Area WHERE Sys_Organization.JDID=Sys_Area.ID", null);

						//更新用户信息里的冗余小区信息
						ExecuteNonQueryWithinTrans(conn, trans, "UPDATE Sys_User SET XQID=Sys_Organization.PID FROM Sys_Organization WHERE Sys_User.OrgID=Sys_Organization.ID", null);

						trans.Commit();
					}
					catch (Exception ex)
					{
						throw ex;
					}
				}

				conn.Close();
			}
			#endregion
		}

		/// <summary>
		/// 获取下级单位列表
		/// </summary>
		public PageData GetChildList(int id, UserSession sessionCurrent)
		{
			PageData result = null;
			try
			{
				var query = (from u in context.SysOrganization
							 where u.PID == id
							 select new { u.ID, u.Name }).AsNoTracking();
				result = new PageData();
				result.list = query.ToList();
			}
			catch (BuzException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				logger.LogError(ex.InnerException ?? ex, "获取分页数据出错");
				throw new BuzException("获取分页数据失败，" + (ex.InnerException != null ? ex.InnerException.Message : ex.Message));
			}
			return result;
		}
	}
}